package com.itcj.dmp

import org.apache.kudu.ColumnSchema.ColumnSchemaBuilder
import org.apache.kudu.{Schema, Type}

import scala.collection.JavaConverters._

object ConstantSchema {


  def odsSchema: Schema = {

    new Schema(List(
      new ColumnSchemaBuilder("uuid", Type.STRING).nullable(false).key(true).build,
      new ColumnSchemaBuilder("sessionid", Type.STRING).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("advertisersid", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adorderid", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adcreativeid", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adplatformproviderid", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("sdkversion", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adplatformkey", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("putinmodeltype", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("requestmode", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adprice", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adppprice", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("requestdate", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("ip", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("appid", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("appname", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("device", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("client", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("osversion", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("density", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("pw", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("ph", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("longitude", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("latitude", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("region", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("city", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("ispid", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("ispname", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("networkmannerid", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("networkmannername", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("iseffective", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("isbilling", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adspacetype", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adspacetypename", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("devicetype", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("processnode", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("apptype", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("district", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("paymode", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("isbid", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("bidprice", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("winprice", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("iswin", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("cur", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("rate", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("cnywinprice", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("imei", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("mac", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("idfa", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("openudid", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("androidid", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("rtbprovince", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("rtbcity", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("rtbdistrict", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("rtbstreet", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("storeurl", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("realip", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("isqualityapp", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("bidfloor", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("aw", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("ah", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("imeimd5", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("macmd5", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("idfamd5", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("openudidmd5", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("androididmd5", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("imeisha1", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("macsha1", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("idfasha1", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("openudidsha1", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("androididsha1", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("uuidunknow", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("userid", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("reqdate", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("reqhour", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("iptype", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("initbidprice", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adpayment", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("agentrate", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("lomarkrate", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("adxrate", Type.DOUBLE).nullable(true).key(false).build,
      new ColumnSchemaBuilder("title", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("keywords", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("tagid", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("callbackdate", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("channelid", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("mediatype", Type.INT64).nullable(true).key(false).build,
      new ColumnSchemaBuilder("email", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("tel", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("age", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("sex", Type.STRING).nullable(true).key(false).build,
      new ColumnSchemaBuilder("geoHash", Type.STRING).nullable(true).key(false).build
    ).asJava)
  }


  /**
    * 分析广告指标的SQL语句
    * @param sourceTableName
    * @return
    */
  def kpiSQL(sourceTableName: String):String = {
    s"""
       |SELECT t.region,
       |       t.city,
       |       sum(CASE
       |               WHEN (t.requestmode = 1
       |                     AND t.processnode >= 1) THEN 1
       |               ELSE 0
       |           END) AS orginal_req_cnt,
       |       sum(CASE
       |               WHEN (t.requestmode = 1
       |                     AND t.processnode >= 2) THEN 1
       |               ELSE 0
       |           END) AS valid_req_cnt,
       |       sum(CASE
       |               WHEN (t.requestmode = 1
       |                     AND t.processnode = 3) THEN 1
       |               ELSE 0
       |           END) AS ad_req_cnt,
       |       sum(CASE
       |               WHEN (t.adplatformproviderid >= 100000
       |                     AND t.iseffective = 1
       |                     AND t.isbilling =1
       |                     AND t.isbid = 1
       |                     AND t.adorderid != 0) THEN 1
       |               ELSE 0
       |           END) AS join_rtx_cnt,
       |       sum(CASE
       |               WHEN (t.adplatformproviderid >= 100000
       |                     AND t.iseffective = 1
       |                     AND t.isbilling =1
       |                     AND t.iswin = 1) THEN 1
       |               ELSE 0
       |           END) AS success_rtx_cnt,
       |       sum(CASE
       |               WHEN (t.requestmode = 2
       |                     AND t.iseffective = 1) THEN 1
       |               ELSE 0
       |           END) AS ad_show_cnt,
       |       sum(CASE
       |               WHEN (t.requestmode = 3
       |                     AND t.iseffective = 1) THEN 1
       |               ELSE 0
       |           END) AS ad_click_cnt,
       |       sum(CASE
       |               WHEN (t.requestmode = 2
       |                     AND t.iseffective = 1
       |                     AND t.isbilling = 1) THEN 1
       |               ELSE 0
       |           END) AS media_show_cnt,
       |       sum(CASE
       |               WHEN (t.requestmode = 3
       |                     AND t.iseffective = 1
       |                     AND t.isbilling = 1) THEN 1
       |               ELSE 0
       |           END) AS media_click_cnt,
       |       sum(CASE
       |               WHEN (t.adplatformproviderid >= 100000
       |                     AND t.iseffective = 1
       |                     AND t.isbilling = 1
       |                     AND t.iswin = 1
       |                     AND t.adorderid > 20000
       |                     AND t.adcreativeid > 200000) THEN floor(t.winprice / 1000)
       |               ELSE 0
       |           END) AS dsp_pay_money,
       |       sum(CASE
       |               WHEN (t.adplatformproviderid >= 100000
       |                     AND t.iseffective = 1
       |                     AND t.isbilling = 1
       |                     AND t.iswin = 1
       |                     AND t.adorderid > 20000
       |                     AND t.adcreativeid > 200000) THEN floor(t.adpayment / 1000)
       |               ELSE 0
       |           END) AS dsp_cost_money
       |FROM $sourceTableName t
       |GROUP BY t.region,
       |         t.city
    """.stripMargin
  }


  /**
    * 统计竞价成功占比SQL
    * @param kpiTableName
    * @return
    */
  def rateSQL(kpiTableName: String): String = {
    s"""
       |SELECT t.*,
       |       t.success_rtx_cnt / t.join_rtx_cnt AS success_rtx_rate,
       |       t.media_click_cnt / t.ad_click_cnt AS ad_click_rate
       |FROM $kpiTableName t
       |WHERE t.success_rtx_cnt != 0
       |  AND t.join_rtx_cnt != 0
       |  AND t.media_click_cnt != 0
       |  AND t.ad_click_cnt != 0
      """.stripMargin
  }

  /**
    * 地域广告统计 落地Kudu的表名
    */
  val adsRegionTargetTableSchema: Schema =  new Schema(
    List(
      new ColumnSchemaBuilder("region", Type.STRING).nullable(false).key(true).build(),
      new ColumnSchemaBuilder("city", Type.STRING).nullable(false).key(true).build(),
      new ColumnSchemaBuilder("orginal_req_cnt", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("valid_req_cnt", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("ad_req_cnt", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("join_rtx_cnt", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("success_rtx_cnt", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("ad_show_cnt", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("ad_click_cnt", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("media_show_cnt", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("media_click_cnt", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("dsp_pay_money", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("dsp_cost_money", Type.INT64).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("success_rtx_rate", Type.DOUBLE).nullable(true).key(false).build(),
      new ColumnSchemaBuilder("ad_click_rate", Type.DOUBLE).nullable(true).key(false).build()
    ).asJava
  )
}
